package nutella89.basic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class J20_BookDBDAO 
				implements J20_BookDBService {

	// JDBC 접속정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 정의
	private static final String insertSQL = 
		"insert into bookdb "
		+ " ( id,title,publisher,author ) "
	+ " values ( bookid.nextval, ?,?,?) ";
	
	private static final String selectSQL =
		" select id, title from bookdb "
		+ " order by id desc ";
	
	private static final String selectOneSQL =
		" select * from bookdb "
		+ " where id = ? ";
	
	private static final String updateSQL =
		" update bookdb set title=?, "
		+ " publisher=?, author=?,"
		+ " regdate=sysdate "
		+ " where id = ? ";
		
	private static final String deleteSQL =
		" delete from bookdb "
		+ " where id = ? ";
	
	@Override
	public int insertBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager
				.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(insertSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
			cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally {
			if(ps != null)
			try{ps.close();}catch(Exception e){}
			if(conn != null)
			try{conn.close();}catch(Exception e){}
		}
		
		return cnt;
	}

	@Override
	public String selectBookDB() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null; // 추가됨!@@
		String result = "";	//ArrayList로 변경예정
		
		try {
			Class.forName(DRV);
			conn = DriverManager
				.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			
			// select문 실행 후 결과 처리
			while(rs.next()) {
				for(int i=1; i<=2 ;++i)
					result += rs.getString(i)+" ";
				result += "\n";
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if(rs != null)
			 try {rs.close();}catch(Exception e){}
			if(ps != null)
			 try {ps.close();}catch(Exception e){}
			if(conn != null)
			 try {conn.close();}catch(Exception e){}
		}
		
		return result;
	}

	@Override
	public String selectBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try {
			Class.forName(DRV);
			conn = DriverManager
				.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(selectOneSQL);
				ps.setString(1, id);
			rs = ps.executeQuery();
			
			while(rs.next()){
				for(int i=1; i<=5; ++i)
					result += rs.getString(i)+" ";
				result += "\n";
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally{
			if(rs != null)
			 try {rs.close();}catch(Exception e){}
			if(ps != null)
			 try {ps.close();}catch(Exception e){}
			if(conn != null)
			 try {conn.close();}catch(Exception e){}
		}
		
		return result;
	}

	@Override
	public int updateBookDB(J20_BookDBVO bvo) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try{
			Class.forName(DRV);
			conn = DriverManager
				.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(updateSQL);
				ps.setString(1, bvo.getTitle());
				ps.setString(2, bvo.getPublisher());
				ps.setString(3, bvo.getAuthor());
				ps.setString(4, bvo.getId());
			cnt = ps.executeUpdate();	
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if(ps != null)
			 try {ps.close();}catch(Exception e){}
			if(conn != null)
			 try {conn.close();}catch(Exception e){}			
		}
		
		return cnt;
	}

	@Override
	public int deleteBookDB(String id) {
		Connection conn = null;
		PreparedStatement ps = null;
		int cnt = 0;
		
		try {
			Class.forName(DRV);
			conn = DriverManager
				.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(deleteSQL);
				ps.setString(1, id);
			cnt = ps.executeUpdate();	
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if(ps != null)
			 try {ps.close();}catch(Exception e){}
			if(conn != null)
			 try {conn.close();}catch(Exception e){}			
		}
		
		return cnt;
	}

}












